package com.eatables.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


/**
 * Provides utilities for database access
 */
public class DBUtil {

   public static Connection               connection = null;

   /**
    * Produces a string representation of a complete result set
    * 
    * @param rs
    *           The ResultSet to be displayed
    * @return The string representation
    * @throws SQLException
    *            to indicate a problem with the ResultSet
    */
   public static String dumpResultSet (ResultSet rs) throws SQLException {
      StringBuffer buf = new StringBuffer();
      if (rs != null) {
         ResultSetMetaData rsmd = rs.getMetaData();
         int columnCount = rsmd.getColumnCount() + 1;
         for (int i = 1; i < columnCount; i++) {
            buf.append(rsmd.getColumnLabel(i));
            buf.append("  ");
         }
         buf.append("\n===========================================\n");

         while (rs.next()) {
            for (int i = 1; i < columnCount; i++) {
               buf.append(rs.getObject(i));
               buf.append("  ");
            }
            buf.append("\n");
         }
      }
      return buf.toString();
   }

   /**
    * create a list of map objects. each map object represents a row within the supplied result set that is key against
    * the column name
    * 
    * @return List the list of map objects for each row in the ResultSet
    * @param rs
    *           the ResultSet to extract the data from
    * @throws SQLException
    *            report database problems extracting details from the ResultSet
    */
   @SuppressWarnings ("unchecked")
   public static List buildListOfMapRecords (ResultSet rs) throws SQLException {
	   System.out.println("Enterting into buildListOfMapRecords");
      List l = new LinkedList();
      if (rs != null) {
         ResultSetMetaData rsmd = rs.getMetaData();
         int columnCount = rsmd.getColumnCount() + 1;
         while (rs.next()) {
            Map map = new HashMap();
            for (int i = 1; i < columnCount; i++) {
               map.put(rsmd.getColumnLabel(i), rs.getObject(i));
            }
            l.add(map);
         }
      }
      closeConnection(connection);
      closeResultSet(rs);
      // closeStatement(st);
      return l;
   }

   /*
    * Connects to the Database
    */

   public static Connection connect () {
	   System.out.println("entering into Connect()");
      try {
         // Load the JDBC driver
         String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
         try {
            Class.forName(driverName).newInstance();
         } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
         } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
         }

         // Create a connection to the database
         // String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url

         /*
          * String url = "jdbc:mysql://localhost:3306/eatables?zeroDateTimeBehavior=convertToNull"; String username =
          * "root"; String password = "admin";
          */

         String url = "jdbc:mysql://testeatables.db.2783256.hostedresource.com?zeroDateTimeBehavior=convertToNull";
         String username = "testeatables";
         String password = "Tyarala123";
         connection = DriverManager.getConnection(url, username, password);
         // connection = DriverManager.getConnection(url);
      } catch (ClassNotFoundException e) {
         // Could not find the database driver
    	  System.out.println("Exception "+ e);
      } catch (SQLException e) {
         // Could not connect to the database
    	  System.out.println("Exception "+ e);
      }
      // Logger Checking
      // log.trace("Trace");
      // log.debug("Debug");
      // log.info("Info");
      // log.warn("Warn");
      // log.error("Error");
      // log.fatal("Fatal");
      return connection;
   }

   public static ResultSet executeMyQuery (String query) {
	   System.out.println("Entering executeMyQuery");

      ResultSet rs = null;
      try {

         Statement st = DBUtil.connect().createStatement();
         st.executeUpdate("USE testeatables");
         rs = st.executeQuery(query);
      } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      return rs;

   }

   public static int insertQuery (String query) {
	   System.out.println("entering into insertQuery");
      int status = 0;
      try {
         Statement st = DBUtil.connect().createStatement();
         st.executeUpdate("USE testeatables");
         status = st.executeUpdate(query);
         System.out.println("Inserted the Data and the status is " + status);
      } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }
      return status;

   }

   public static Connection getConnection () {

      return connection;
   }

   public static void setConnection (Connection connection) {
      DBUtil.connection = connection;
   }

   /**
    * Closes the database connection specified in the parameter
    * 
    * @param conn
    *           database connection to close
    */

   public static void closeConnection (Connection conn) {
	   System.out.println("entering into closeConnection");
      try {
         if (conn != null && !conn.isClosed()) {
            // Log.debug(DBUtils.class, "Closing connection [" + conn.toString() + "]");
            conn.close();
            // Log.debug(DBUtils.class, "Connectionis now closed? [" + conn.isClosed() + "]");
         }
      } catch (SQLException e) {
         System.out.println("DBUtils: SQL Exception while closing DB connection : \n" + e);
      }
   }

   /**
    * Closes the result set specified in the parameter
    * 
    * @param rset
    *           result set to close
    */

   public static void closeResultSet (ResultSet rset) {
	   System.out.println("entering into closeResultSet");
      try {
         if (rset != null) {
            rset.close();
            rset = null;
         }
      } catch (SQLException e) {
         System.out.println("DBUtils: SQL Exception while closing Result Set : \n" + e);
      }
   }

   /**
    * Closes the statement specified in the parameter
    * 
    * @param stmt
    *           statement to close
    */

   public static void closeStatement (Statement stmt) {
	   System.out.println("entering into closeStatement");
      try {
         if (stmt != null)
            stmt.close();
      } catch (SQLException e) {
         System.out.println("DBUtils: SQL Exception while closing Statement : \n" + e);
      }
   }

}
